package com.xinsoft.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.CharsetUtil;
import com.xinsoft.common.exception.BDException;
import com.xinsoft.constant.Const;
import com.xinsoft.entity.dto.ExcelErrRowDTO;
import com.xinsoft.entity.po.SysFileHis;
import com.xinsoft.service.SysFileHisService;
import org.apache.poi.ss.usermodel.*;
import org.springframework.util.CollectionUtils;
import org.springframework.util.FileCopyUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 文件名称： java</br>
 * 初始作者： hsm</br>
 * 创建日期： 2023年05月19日</br>
 * 功能说明： Excel操作工具 <br/>
 * =================================================<br/>
 * 修改记录：<br/>
 * 修改作者 日期 修改内容<br/>
 * ================================================<br/>
 * Copyright (c) 2019-2020 .All rights reserved.<br/>
 */
public class ExcelUtils {

    /**
     * 方法描述: [excel流化.]</br>
     */
    public static InputStream writeExcelToStream(Workbook workbook) {
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try {
            workbook.write(bos);
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        // 数据在bos中
        ByteArrayInputStream bis = new ByteArrayInputStream(bos.toByteArray());
        return bis;
    }

    /**
     * 方法描述: [下载excel.]</br>
     */
    public static void downloadExcel(InputStream inputStream, HttpServletResponse response, String excelName) {
        response.setContentType("application/x-msdownload");
        response.setHeader("Content-Disposition", "attachment;" + "filename=" + excelName);
        try {
            FileCopyUtils.copy(inputStream, response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 方法描述: [通过easyPoi导出.]</br>
     */
    public static void exportByEasypoi(List<?> list, Class<?> clazz, String title, String sheet,
                                       HttpServletResponse response,String fileName) throws Exception {
        // ExportParams中，第1个参数为数据表格的标题，第2个参数为sheet的名称
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(title, sheet), clazz, list);
        String logicalName = URLEncoder.encode(fileName, CharsetUtil.UTF_8);
        downloadExcel(ExcelUtils.writeExcelToStream(workbook), response, logicalName + ".xls");
    }

    /**
     * 方法描述: [通过easyPoi导出错误文件.]</br>
     */
    public static String exportErrRows(List<ExcelErrRowDTO> errRowDOs, MultipartFile multipartFile, SysFileHisService sysFileHisService){

        if (CollectionUtils.isEmpty(errRowDOs)){
            //无错误行，则正常结束。
            return null;
        }
        try {
            InputStream in = multipartFile.getInputStream();
            Workbook oldWb = WorkbookFactory.create(in);
            Sheet oldSheet = oldWb.getSheetAt(0);

            // 创建一个新的excel用来导出错误行。
            Workbook errWorkBook =  WorkbookFactory.create(multipartFile.getInputStream()) ;
            Sheet errSheet = errWorkBook.getSheetAt(0);
            errSheet.shiftRows(2,errSheet.getLastRowNum(),1);
            Row errHeaderRow = errSheet.getRow(1);
            int columnCount = errHeaderRow.getLastCellNum();
            // 添加失败列
            Cell failureReasonCol = errHeaderRow.createCell(columnCount);
            failureReasonCol.setCellStyle(errHeaderRow.getCell(columnCount-1).getCellStyle());
            failureReasonCol.setCellValue("失败原因");

            //创建空单元格style
            CellStyle blockCellStyle = errWorkBook.createCellStyle();
            blockCellStyle.setBorderBottom(BorderStyle.THIN);
            blockCellStyle.setBorderLeft(BorderStyle.THIN);
            blockCellStyle.setBorderTop(BorderStyle.THIN);
            blockCellStyle.setBorderRight(BorderStyle.THIN);

            //创建错误信息单元格style
            CellStyle errCellStyle = errWorkBook.createCellStyle();
            errCellStyle.setBorderBottom(BorderStyle.THIN);
            errCellStyle.setBorderLeft(BorderStyle.THIN);
            errCellStyle.setBorderTop(BorderStyle.THIN);
            errCellStyle.setBorderRight(BorderStyle.THIN);
            Font dataFont = errWorkBook.createFont();
            dataFont.setColor(Font.COLOR_RED);
            errCellStyle.setFont(dataFont);

            // 将错误行拷贝到错误原因excel中
            int index = 2;
            for (ExcelErrRowDTO errRowDO : errRowDOs) {
                Integer oldIndex = errRowDO.getRowIndex();
                Row errRow = errSheet.createRow(index);
                index++;
                Row oldRow = oldSheet.getRow(oldIndex);

                for (int i = 0; i <= columnCount; i++) {
                    // Grab a copy of the old/new cell
                    Cell oldCell = oldRow.getCell(i);
                    Cell newCell = errRow.createCell(i);

                    // 设置错误信息
                    if(i == columnCount){
                        newCell.setCellStyle(errCellStyle);
                        newCell.setCellValue(errRowDO.getErrMessage());
                        break;
                    }
                    // If the old cell is null jump to next cell
                    if(oldCell==null){
                        newCell.setCellStyle(blockCellStyle);
                        continue;
                    }

                    // Copy style from old cell and apply to new cell
                    CellStyle newCellStyle = errWorkBook.createCellStyle();
                    newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
                    ;
                    newCell.setCellStyle(newCellStyle);

                    // If there is a cell comment, copy
                    if (oldCell.getCellComment() != null) {
                        newCell.setCellComment(oldCell.getCellComment());
                    }

                    // If there is a cell hyperlink, copy
                    if (oldCell.getHyperlink() != null) {
                        newCell.setHyperlink(oldCell.getHyperlink());
                    }

                    switch (oldCell.getCellType()) {
                        case BLANK:
                            newCell.setCellValue(oldCell.getStringCellValue());
                            break;
                        case BOOLEAN:
                            newCell.setCellValue(oldCell.getBooleanCellValue());
                            break;
                        case ERROR:
                            newCell.setCellErrorValue(oldCell.getErrorCellValue());
                            break;
                        case FORMULA:
                            newCell.setCellFormula(oldCell.getCellFormula());
                            break;
                        case NUMERIC:
                            newCell.setCellValue(oldCell.getNumericCellValue());
                            break;
                        case STRING:
                            newCell.setCellValue(oldCell.getRichStringCellValue());
                            break;
                        default:
                            throw new IllegalStateException("Unexpected value: " + oldCell.getCellType());
                    }
                }
            }
            ByteArrayOutputStream  out = new ByteArrayOutputStream();
            errWorkBook.write(out);

            String strTime = DateUtil.format(new Date(),"yyyyMMddHHmmss");
            //InputStream inputStream = new ByteArrayInputStream(out.toByteArray());
            InputStream inputStream2 = new ByteArrayInputStream(out.toByteArray());
            String errFileName = "("+strTime+")"+multipartFile.getOriginalFilename();
            //ExcelUtils.downloadExcel(inputStream,response,errFileName);

            String fileDesc = "失败条数："+errRowDOs.size()+"；上传时间："+strTime;
            return saveErrorFile(errFileName,fileDesc,sysFileHisService,inputStream2);

        } catch (Exception e) {
            e.printStackTrace();
            throw new BDException("导入失败，系统异常！");
        }


    }


    /**
     * 方法描述: [取出list中存在重复的元素.]</br>
     */
    public static <E> List<E> getDuplicateElements(List<E> list) {
        return list.stream() // list 对应的 Stream
                .collect(Collectors.toMap(e -> e, e -> 1, Integer::sum)) // 获得元素出现频率的 Map，键为元素，值为元素出现的次数
                .entrySet().stream() // 所有 entry 对应的 Stream
                .filter(entry -> entry.getValue() > 1) // 过滤出元素出现次数大于 1 的 entry
                .map(Map.Entry::getKey) // 获得 entry 的键（重复元素）对应的 Stream
                .collect(Collectors.toList());  // 转化为 List
    }

    /**
     * 保存错误文件
     * @param errFileName
     * @param fileDesc
     * @param sysFileHisService
     * @param inputStream
     * @throws IOException
     */
    private static String saveErrorFile(String errFileName,String fileDesc,SysFileHisService sysFileHisService,InputStream inputStream) throws IOException {
        saveFile(inputStream,errFileName);


        SysFileHis fileHis = new SysFileHis();
        fileHis.setFileName(errFileName);
        fileHis.setFileTypeId(Const.FileType.IMPORT_ERR_FILE);
        fileHis.setFileUploadTime(DateUtil.formatDateTime(new Date()));
        fileHis.setFileDesc(fileDesc);
        fileHis.setFileUrl(Const.FileTypeProxyUrl.IMPORT_ERROR_FILE_URL + "/" + fileHis.getFileName());
        sysFileHisService.save(fileHis);
        return fileHis.getFileUrl();
    }


    /**
     * 保存文件
     * @param inStream
     * @param errFileName
     * @return
     * @throws IOException
     */
    public static void saveFile(InputStream inStream, String errFileName) throws IOException {

        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        //创建一个Buffer字符串
        byte[] buffer = new byte[1024];
        //每次读取的字符串长度，如果为-1，代表全部读取完毕
        int len = 0;
        //使用一个输入流从buffer里把数据读取出来
        while ((len = inStream.read(buffer)) != -1) {
            //用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度
            outStream.write(buffer, 0, len);
        }
        //关闭输入流
        inStream.close();
        //把outStream里的数据写入内存

        //得到图片的二进制数据，以二进制封装得到数据，具有通用性
        byte[] data = outStream.toByteArray();
        File file = new File("/data/import-err-file/" + errFileName);
        //创建输出流
        FileOutputStream fileOutStream = new FileOutputStream(file);
        //写入数据
        fileOutStream.write(data);
        fileOutStream.flush();
        fileOutStream.close();
    }
}
